package com.aaa.sbms.dao;

import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

public interface SalDao {

    /**
     * 查询职位
     * @return
     */
    @Select("select * from positions where pid!=2")
    List<Map> positions();

    /**
     * 分页查询职位工资
     * @param map
     * @return
     */
    @Select("<script>" +
            "select sp.id,p.pid,p.pname,sp.sal\n" +
            "from salarys_positions sp\n" +
            "left join positions p on p.pid=sp.positionid\n" +
            "<where>" +
            "<if test=\"pname!=null and pname!=''\">and p.pname like '%${pname}%'</if>" +
            "</where>" +
            "order by p.pname asc limit #{begin},#{pageSize}" +
            "</script>")
    List<Map> salPage(Map map);

    /**
     * 查询职位工资总数量
     * @param map
     * @return
     */
    @Select("<script>" +
            "select count(*)\n" +
            "from salarys_positions sp\n" +
            "left join positions p on p.pid=sp.positionid\n" +
            "<where>" +
            "<if test=\"pname!=null and pname!=''\">and p.pname like '%${pname}%'</if>" +
            "</where>" +
            "</script>")
    int salPageCount(Map map);

    /**
     * 分页查询工资结算
     * @param map
     * @return
     */
    @Select("<script>" +
            "select id,userid,username,sal,comn,fine,tax,realwages," +
            "date_format(insertdate,'%Y-%m-%d %H:%i:%s') insertdate\n" +
            "from salarys\n" +
            "<where>" +
            "<if test=\"userid!=null and userid!=''\">and userid like '%${userid}%'</if>" +
            "<if test=\"username!=null and username!=''\">and username like '%${username}%'</if>" +
            "<if test=\"time!='' and time!=null\">and date_format(insertdate,'%Y-%m') = #{time}</if>" +
            "</where>" +
            "order by insertdate desc limit #{begin},#{pageSize}" +
            "</script>")
    List<Map> salOnTimePage(Map map);

    /**
     * 查询工资结算总数量
     * @param map
     * @return
     */
    @Select("<script>" +
            "select count(*)"+
            "from salarys \n" +
            "<where>" +
            "<if test=\"userid!=null and userid!=''\">and userid like '%${userid}%'</if>" +
            "<if test=\"username!=null and userid!=''\">and username like '%${username}%'</if>" +
            "<if test=\"time!=null and time!=''\">and date_format(insertdate,'%Y-%m') = #{time}</if>" +
            "</where>" +
            "</script>")
    int salOnTimePageCount(Map map);

    /**
     * 根据id查询一条职位工资记录
     * @param id
     * @return
     */
    @Select("<script>" +
            "select sp.id,p.pid,p.pname,sp.sal\n" +
            "from salarys_positions sp\n" +
            "left join positions p on p.pid=sp.positionid\n" +
            "where sp.id=#{id}" +
            "</script>")
    Map salOne(int id);

    @Update("update salarys_positions set sal=#{sal} where id=#{id}")
    int updateSal(Map map);
}
